package helloworld.dao.msaccess;

import helloworld.dao.TeacherDao;

import helloworld.admin.bean.TeacherAdminBean;
import helloworld.dao.DAOException;
import helloworld.exception.DuplicateTeacherException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import helloworld.exception.ErrorLevel;

import java.util.Properties;
import java.util.ResourceBundle;
import java.util.Enumeration;
import helloworld.common.DbSQLCommon;

/**
 * TeacherDAO encapsulates data access and manipulation It represents the Data
 * Access Object (DAO) Pattern. A DAO abstract the mechanisms of accessing and
 * manipulating a persistence store such as database,
 * 
 * using SQLParameter-like processor
 * 
 * @author TUYEN-DM
 * @version $Revision: $ $Date: 2009/05/13 $
 */
public class TeacherDAO_AccessImpl implements TeacherDao {

	private DbSQLCommon clsDbSQLCommon;

	protected static String SELECT_SQL = "";
	protected static String SEARCH_SQL = "";
	protected static String INSERT_SQL = "";
	protected static String UPDATE_SQL = "";
	protected static String DELETE_SQL = "";
	protected static String DELETE_ID_SQL = "";

	/**
	 * Constructor
	 */
	public TeacherDAO_AccessImpl() {
		clsDbSQLCommon = new DbSQLCommon();
	}

	/**
	 * Search teacher by teacher name
	 * 
	 * @param emailAddress
	 * @return
	 * @throws DAOException
	 */
	public TeacherAdminBean fetchDetail(String teacherName) throws DAOException {

		PreparedStatement pSmnt = null;

		Connection conn = getMySQLConnnection();
		TeacherAdminBean teacher = null;

		try {
			SELECT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"teacher.select");
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setString(1, teacherName);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				teacher = extractTeacherDetailFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { teacherName };
			throw new DAOException(se, "error.database.fetch.teacherdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return teacher;
	}

	/**
	 * Search teacher by teacherID 2009/05/09
	 * 
	 * @param teacherID
	 * @return
	 * @throws DAOException
	 */
	public TeacherAdminBean fetchDetailID(int teacherID) throws DAOException {

		PreparedStatement pSmnt = null;

		Connection conn = getMySQLConnnection();
		TeacherAdminBean teacher = null;

		try {
			SELECT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"teacher.selectID");
			pSmnt = conn.prepareStatement(SELECT_SQL);
			pSmnt.setInt(1, teacherID);// set parameter 1

			ResultSet set = pSmnt.executeQuery();
			if (set.next()) {
				teacher = extractTeacherDetailFromResultSet(set);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { teacherID + "" };
			throw new DAOException(se, "error.database.fetch.teacherdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return teacher;
	}

	/**
	 * 
	 * @author Tuyen-DM
	 * @param teacherName
	 * @return
	 * @throws DAOException
	 */
	public TeacherAdminBean[] find(String teacherName) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();
		TeacherAdminBean teacher = null;
		List list = new ArrayList();

		try {
			SEARCH_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"teacher.select");
			pSmnt = conn.prepareStatement(SEARCH_SQL);
			pSmnt.setString(1, "%" + teacherName + "%");// search LIKE

			ResultSet set = pSmnt.executeQuery();
			while (set.next()) {
				teacher = extractTeacherDetailFromResultSet(set);
				list.add(teacher);
			}
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { teacherName };
			throw new DAOException(se, "error.database.fetch.teacherdetail",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		TeacherAdminBean[] teachers = null;
		if (list.size() > 0) {
			teachers = (TeacherAdminBean[]) list.toArray(new TeacherAdminBean[list
					.size()]);
		}

		return teachers;
	}

	/**
	 * insert new teacher into database
	 * 
	 * @param teacher
	 * @return
	 * @throws DAOException
	 * @throws DuplicateTeacherException
	 */
	public int create(TeacherAdminBean teacher) throws DAOException,
			DuplicateTeacherException {
		int intInsertedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			TeacherAdminBean det = fetchDetail(teacher.getTeacherName());
			if (det != null) {
				intInsertedRows = -1;
				throw new DuplicateTeacherException(
						"error.database.teacher.duplicate", teacher
								.getTeacherName(), null, ErrorLevel.WARNING);
			} else {
				INSERT_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
						"teacher.insert");
				pStmt = conn.prepareStatement(INSERT_SQL);
				pStmt.setString(1, teacher.getTeacherName());
				pStmt.setString(2, teacher.getAddress());
				pStmt.setString(3, teacher.getPhone());
				pStmt.setString(4, teacher.getEmail());
				pStmt.setString(5, teacher.getCountry());
				pStmt.setInt(6, teacher.getExperiencedYears());
				pStmt.setString(7, teacher.getPhotoUrl());
				pStmt.setString(8, teacher.getBirthDate());

				intInsertedRows = pStmt.executeUpdate();
			}

		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { teacher.getTeacherName() };
			throw new DAOException(se, "error.database.teacher.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}

		return intInsertedRows;
	}

	/**
	 * update teacher information
	 * 
	 * @param teacher
	 * @return
	 * @throws DAOException
	 * @throws DuplicateTeacherException
	 */
	public int update(TeacherAdminBean teacher) throws DAOException,
			DuplicateTeacherException {
		int intUpdatedRows = 0;
		PreparedStatement pStmt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			TeacherAdminBean det = fetchDetailID(teacher.getTeacherID());

			if (det != null
					&& !det.getTeacherName().equals(teacher.getTeacherName())) {
				TeacherAdminBean det2 = fetchDetail(teacher.getTeacherName());
				if (det2 != null
						&& det2.getTeacherName().equals(teacher.getTeacherName())) {
					throw new DuplicateTeacherException(
							"error.database.teacher.duplicate", teacher
									.getTeacherName(), null, ErrorLevel.WARNING);
				}
			}

			UPDATE_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"teacher.update");
			pStmt = conn.prepareStatement(UPDATE_SQL);
			pStmt.setString(1, teacher.getTeacherName());
			pStmt.setString(1, teacher.getTeacherName());
			pStmt.setString(2, teacher.getAddress());
			pStmt.setString(3, teacher.getPhone());
			pStmt.setString(4, teacher.getEmail());
			pStmt.setString(5, teacher.getCountry());
			pStmt.setInt(6, teacher.getExperiencedYears());
			pStmt.setString(7, teacher.getPhotoUrl());
			pStmt.setString(8, teacher.getBirthDate());
			pStmt.setInt(9, teacher.getTeacherID());

			intUpdatedRows = pStmt.executeUpdate();

		} catch (SQLException se) {
			String[] valueReplacementArray = { teacher.getTeacherName() };
			throw new DAOException(se, "error.database.teacher.save",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
		return intUpdatedRows;
	}

	/**
	 * delete teacher
	 * 
	 * @param emailAddress
	 * @throws DAOException
	 */
	public void delete(int id) throws DAOException {
		PreparedStatement pSmnt = null;
		// Connection conn = getConnection();
		Connection conn = getMySQLConnnection();

		try {
			DELETE_ID_SQL = clsDbSQLCommon.fncGetResourceSQL("db-sql",
					"teacher.deleteID");
			pSmnt = conn.prepareStatement(DELETE_ID_SQL);
			pSmnt.setInt(1, id);
			pSmnt.executeUpdate();
		} catch (SQLException se) {
			// TODO: handle exception
			String[] valueReplacementArray = { new Integer(id).toString() };
			throw new DAOException(se, "error.database.teacher.delete",
					valueReplacementArray, ErrorLevel.ERROR);
		} finally {
			try {
				conn.close();
			} catch (SQLException sq) {
			}
		}
	}

	/**
	 * @param rs
	 *            <p>
	 *            Set results to data object: teacher
	 *            </p>
	 * 
	 */
	protected TeacherAdminBean extractTeacherDetailFromResultSet(ResultSet rs)
			throws SQLException {
		TeacherAdminBean teacher = new TeacherAdminBean();
		
		teacher.setTeacherID(rs.getInt("TeacherID"));
		teacher.setTeacherName(rs.getString("TeacherName"));
		teacher.setAddress(rs.getString("Address"));
		teacher.setOpenDate(rs.getString("Phone"));
		teacher.setEmail(rs.getString("Email"));
		teacher.setCountry(rs.getString("Country"));
		teacher.setExperiencedYears(rs.getInt("ExperiencedYears"));
		teacher.setPhotoUrl(rs.getString("PhotoUrl"));
		teacher.setBirthDate(rs.getString("BirthDate"));

		return teacher;
	}

	/**
	 * database connection processor
	 */
	protected Connection getConnection() throws DAOException {
		Connection dbConnection = null;
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			dbConnection = DriverManager
					.getConnection("jdbc:odbc:helloworld_TRAINING");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * @author Tuyen-DM
	 *         <p>
	 *         Mysql connection test
	 *         </p>
	 * @return
	 * @throws DAOException
	 */
	protected Connection getMySQLConnnection() throws DAOException {
		Connection dbConnection = null;
		try {
			// Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
			// dbConnection =
			// DriverManager.getConnection("jdbc:odbc:helloworld_TRAINING");
			// get database properties test
			Properties prop = new Properties();
			ResourceBundle resource = ResourceBundle.getBundle("db-config");
			Enumeration enumTemp = resource.getKeys();
			while (enumTemp.hasMoreElements()) {
				String key = (String) enumTemp.nextElement();
				prop.put(key, resource.getString(key));
			}

			String userName = prop.getProperty("username");
			String password = prop.getProperty("userpassword");
			String url = prop.getProperty("database_url");
			Class.forName(prop.getProperty("database_Server"));
			dbConnection = DriverManager.getConnection(url, userName, password);
			System.out.println("Database connection established");

		} catch (ClassNotFoundException cfe) {
			throw new DAOException(cfe, "error.database.config", null,
					ErrorLevel.FATAL);
		} catch (SQLException se) {
			throw new DAOException(se, "error.database.config", null,
					ErrorLevel.FATAL);
		}
		return dbConnection;
	}

	/**
	 * debug test
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		try {
			TeacherDAO_AccessImpl dao = new TeacherDAO_AccessImpl();
			TeacherAdminBean teacher = dao.fetchDetail("1");
			System.out.println(teacher);
		} catch (DAOException de) {
			// TODO: handle exception
			Log log = LogFactory.getLog("TeacherDAO");
			de.log(log);
		}
	}
}
